MySQL is one of the most common open source databases in the world, and is also effective. Approximately every popular application language, like Java or PHP, provides a driver to access and run operation with MySQL. Node.js and MySQL are one of the binding requirements for any web application. Before examining stored processes and escaping user input, I will explain how to use the module to link to a MySQL database, execute the normal CRUD operations.
In this tutorial, I will explain the Node.js and MySQL related points. If you’re new to Node and Express then you’re not going to regret taking our Node course.
Here’s how to use MySQL with Nodejs in easy steps:
Step -1: Install the mysql module to your node application
2 3 4 |
npm install mysql –save |
Step-2: Require mysql module in your route file
2 3 4 |
var mysql= require('mysql'); |
Step-3: Create New Database and Database Table in MySql
2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(50), email varchar(50), etype varchar(50), hourlyrate varchar(500), totalhour varchar(500), total varchar(500), PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; |
Step-4: Connecting to the Database
Now, let’s create a file called app.js. Here is the sample code that links to the database and performs a SQL query:
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
var mysql= require('mysql'); var con=mysql.createConnection({ host: "localhost", // database host user: "root", // MySQL username password: "", // MySQL password database: "employee" // your database name }); con.connect(function(err){ if(err) throw err; console.log('database connected successfully'); }); |
Note:- Make sure you have started MySQL on your default port and modified the parameter in the code above.
Now open up your terminal and enter node app.js
command. Once the connection is successfully established you will see the ‘database connected successfully’ message in the console.
Step-5: Now we will perform CURD Operation using Mysql and Express
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 |
var express = require('express'); var path = require('path'); var mysql= require('mysql'); var con=mysql.createConnection({ host: "localhost", user: "root", password: "", database: "employee" }); con.connect(function(err){ if(err) throw err; console.log('database connected successfully'); }); var router = express.Router(); router.use(express.static(__dirname+"./public/")); /// get all records from database router.get('/',function(req, res, next) { var getQuery="select * from `users`"; con.query(getQuery,function(err,result){ if(err) throw err; res.render('index', { title: 'Employee Records', records:result,success:'' }); }); }); //// insert or save records into mysql database table router.post('/', function(req, res, next) { var name= req.body.uname; var email= req.body.email; var etype= req.body.emptype; var hourlyrate= req.body.hrlyrate; var totalHour= req.body.ttlhr; var total= parseInt(req.body.hrlyrate) * parseInt(req.body.ttlhr); var insertQuery='insert into `users` (`name`,`email`,`etype`,`hourlyrate`,`totalhour`,`total`) VALUES (?,?,?,?,?,?)'; var hourlyrate= req.body.hrlyrate; var total= parseInt(req.body.hrlyrate) * parseInt(req.body.ttlhr); var query=mysql.format(insertQuery,[name,email,etype,hourlyrate,totalHour,total]); con.query(query,function(err,response){ if(err) throw err; // console.log(response.insertId); var getQuery="select * from `users`"; con.query(getQuery,function(err,result){ if(err) throw err; res.render('index', { title: 'Employee Records', records:result,success:'Record Inserted Successfully' }); }); }); }); //// Edit records into mysql database table router.get('/edit/:id', function(req, res, next) { var id=req.params.id; var getQuery="select * from `users` where `id`=?"; var query=mysql.format(getQuery,id); con.query(query,function(err,result){ if(err) throw err; var string=JSON.stringify(result); var json = JSON.parse(string); res.render('edit', { title: 'Employee Records', records:json,success:'' }); }); }); //// Update records into mysql database table router.post('/update/', function(req, res, next) { var id= req.body.id; var name= req.body.uname; var email= req.body.email; var etype= req.body.emptype; var hourlyrate= req.body.hrlyrate; var totalHour= req.body.ttlhr; var total= parseInt(req.body.hrlyrate) * parseInt(req.body.ttlhr); var updateQuery='UPDATE `users` SET `name`=? ,`email`=?,`etype`=?,`hourlyrate`=?,`totalhour`=?,`total`=? where `id`=?'; var query=mysql.format(updateQuery,[name,email,etype,hourlyrate,totalHour,total,id]); con.query(query,function(err,response){ if(err) throw err; // console.log(response.insertId); res.redirect('/'); }); }); //// Delete record from mysql database table router.get('/delete/:id', function(req, res, next) { var id=req.params.id; var deleteQuery="delete from `users` where `id`=?"; var query=mysql.format(deleteQuery,id); con.query(query,function(err){ if(err) throw err; res.redirect('/'); }); }); module.exports = router; |
In the above example code, you will get the curd (Save + Update + Read + Delete ) operation process using express and MySQL. You can also watch below video in Hindi with live explanation.
Conclusion :
MySQL is one of the world’s widely used database engine and it really works very well with Nodejs or express.
How to Create Pagination with Node.js, MongoDB, Express and EJS Step by Step
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request
Pradeep Maurya is the Professional Web Developer & Designer and the Founder of “Tutorials website”. He lives in Delhi and loves to be a self-dependent person. As an owner, he is trying his best to improve this platform day by day. His passion, dedication and quick decision making ability to stand apart from others. He’s an avid blogger and writes on the publications like Dzone, e27.co